/***********************************************************************************************************************************
 * Name:	Trace_Create.sql
 * Author:	Frank Figearo (frank.figearo@us.bbaaviation.com|frank@sqlnerd.me)
 * Summary:	Create a server-side trace.
 */
USE master;
DECLARE @TraceFile		NVARCHAR(245);	SET @TraceFile	= DBAdmin.GetInstancePath(N'ErrorDumpDir')+'SQL_Trace'; PRINT @TraceFile;
DECLARE @StopTime		DATETIME;		SET @StopTime	= DATEADD(hh,1,GETDATE());	-- 1hr Default
DECLARE @MaxFileSize	BIGINT;			SET @MaxFileSize= 128;
DECLARE @On				BIT;			SET @On			= 1;
DECLARE @TraceID		INT; -- Trace ID created by dbo.sp_trace_create
DECLARE @ReturnValue	INT;

-- create the trace ================================================================================================================
EXECUTE @ReturnValue= dbo.sp_trace_create @TraceID = @TraceID OUTPUT,
	@options	= 2,				-- TRACE_FILE_ROLLOVER
	@tracefile	= @TraceFile,
	@maxfilesize= @MaxFileSize,
	@stoptime	= @StopTime,
	@filecount	= 8;				-- 8 files x 128MB each = 1GB Max
SELECT @TraceID AS [Trace ID], @ReturnValue AS [Return Value];
/*
 *	Return	Description
 *	 0		No error.
 *	 1		Unknown error.
 *	10		Invalid options. Returned when options specified are incompatible.
 *	12		File not created.
 *	13		Out of memory. Returned when there is not enough memory to perform the specified action.
 *	14		Invalid stop time. Returned when the stop time specified has already happened.
 *	15		Invalid parameters. Returned when the user supplied incompatible parameters.
 */


-- set the events ==================================================================================================================

-- RPC:Starting (11) 
EXECUTE dbo.sp_trace_setevent @TraceID, 11,  1, @On;	-- TextData
EXECUTE dbo.sp_trace_setevent @TraceID, 11,  3, @On;	-- DatabaseID 
EXECUTE dbo.sp_trace_setevent @TraceID, 11,  8, @On;	-- HostName
EXECUTE dbo.sp_trace_setevent @TraceID, 11, 10, @On;	-- ApplicationName
EXECUTE dbo.sp_trace_setevent @TraceID, 11, 11, @On;	-- LoginName 
EXECUTE dbo.sp_trace_setevent @TraceID, 11, 12, @On;	-- SPID 
EXECUTE dbo.sp_trace_setevent @TraceID, 11, 13, @On;	-- Duration
EXECUTE dbo.sp_trace_setevent @TraceID, 11, 14, @On;	-- StartTime
EXECUTE dbo.sp_trace_setevent @TraceID, 11, 15, @On;	-- EndTime
EXECUTE dbo.sp_trace_setevent @TraceID, 11, 16, @On;	-- Reads
EXECUTE dbo.sp_trace_setevent @TraceID, 11, 17, @On;	-- Writes
EXECUTE dbo.sp_trace_setevent @TraceID, 11, 18, @On;	-- CPU
EXECUTE dbo.sp_trace_setevent @TraceID, 11, 35, @On;	-- DatabaseName

-- SQL:BatchStarting (13) 
EXECUTE dbo.sp_trace_setevent @TraceID, 13,  1, @On;	-- TextData
EXECUTE dbo.sp_trace_setevent @TraceID, 13,  3, @On;	-- DatabaseID 
EXECUTE dbo.sp_trace_setevent @TraceID, 13,  8, @On;	-- HostName
EXECUTE dbo.sp_trace_setevent @TraceID, 13, 10, @On;	-- ApplicationName
EXECUTE dbo.sp_trace_setevent @TraceID, 13, 11, @On;	-- LoginName 
EXECUTE dbo.sp_trace_setevent @TraceID, 13, 12, @On;	-- SPID 
EXECUTE dbo.sp_trace_setevent @TraceID, 13, 13, @On;	-- Duration
EXECUTE dbo.sp_trace_setevent @TraceID, 13, 14, @On;	-- StartTime
EXECUTE dbo.sp_trace_setevent @TraceID, 13, 15, @On;	-- EndTime
EXECUTE dbo.sp_trace_setevent @TraceID, 13, 16, @On;	-- Reads
EXECUTE dbo.sp_trace_setevent @TraceID, 13, 17, @On;	-- Writes
EXECUTE dbo.sp_trace_setevent @TraceID, 13, 18, @On;	-- CPU
EXECUTE dbo.sp_trace_setevent @TraceID, 13, 35, @On;	-- DatabaseName

-- SP:Starting (42)
EXECUTE dbo.sp_trace_setevent @TraceID, 42,  1, @On;	-- TextData
EXECUTE dbo.sp_trace_setevent @TraceID, 42,  3, @On;	-- DatabaseID 
EXECUTE dbo.sp_trace_setevent @TraceID, 42,  8, @On;	-- HostName
EXECUTE dbo.sp_trace_setevent @TraceID, 42, 10, @On;	-- ApplicationName
EXECUTE dbo.sp_trace_setevent @TraceID, 42, 11, @On;	-- LoginName 
EXECUTE dbo.sp_trace_setevent @TraceID, 42, 12, @On;	-- SPID 
EXECUTE dbo.sp_trace_setevent @TraceID, 42, 13, @On;	-- Duration
EXECUTE dbo.sp_trace_setevent @TraceID, 42, 14, @On;	-- StartTime
EXECUTE dbo.sp_trace_setevent @TraceID, 42, 15, @On;	-- EndTime
EXECUTE dbo.sp_trace_setevent @TraceID, 42, 16, @On;	-- Reads
EXECUTE dbo.sp_trace_setevent @TraceID, 42, 17, @On;	-- Writes
EXECUTE dbo.sp_trace_setevent @TraceID, 42, 18, @On;	-- CPU
EXECUTE dbo.sp_trace_setevent @TraceID, 42, 35, @On;	-- DatabaseName

-- SP:StmtStarting (44)
EXECUTE dbo.sp_trace_setevent @TraceID, 44,  1, @On;	-- TextData
EXECUTE dbo.sp_trace_setevent @TraceID, 44,  3, @On;	-- DatabaseID 
EXECUTE dbo.sp_trace_setevent @TraceID, 44,  8, @On;	-- HostName
EXECUTE dbo.sp_trace_setevent @TraceID, 44, 10, @On;	-- ApplicationName
EXECUTE dbo.sp_trace_setevent @TraceID, 44, 11, @On;	-- LoginName 
EXECUTE dbo.sp_trace_setevent @TraceID, 44, 12, @On;	-- SPID 
EXECUTE dbo.sp_trace_setevent @TraceID, 44, 13, @On;	-- Duration
EXECUTE dbo.sp_trace_setevent @TraceID, 44, 14, @On;	-- StartTime
EXECUTE dbo.sp_trace_setevent @TraceID, 44, 15, @On;	-- EndTime
EXECUTE dbo.sp_trace_setevent @TraceID, 44, 16, @On;	-- Reads
EXECUTE dbo.sp_trace_setevent @TraceID, 44, 17, @On;	-- Writes
EXECUTE dbo.sp_trace_setevent @TraceID, 44, 18, @On;	-- CPU
EXECUTE dbo.sp_trace_setevent @TraceID, 44, 35, @On;	-- DatabaseName


-- start the trace =================================================================================================================
EXECUTE dbo.sp_trace_setstatus @TraceID, 1;
PRINT N'SELECT * FROM sys.fn_trace_gettable(''' + @TraceFile + N'.trc'', default);';
GO


-- check trace status ==============================================================================================================
DECLARE @property TABLE (PropertyID INT NOT Null PRIMARY KEY CLUSTERED, PropertyText VARCHAR(9) NOT Null);
INSERT INTO @property VALUES (1,'Options'),(2,'File Name'),(3,'Max Size'),(4,'Stop Time'),(5,'Status');
SELECT TraceID, PropertyText AS Property, Value FROM sys.fn_trace_getinfo(default) i INNER JOIN @property p ON i.Property = p.PropertyID;
GO

-- stop the trace ==================================================================================================================
/*
DECLARE @TraceID INT= 2;
EXECUTE dbo.sp_trace_setstatus @TraceID, 0;	-- Stop
EXECUTE dbo.sp_trace_setstatus @TraceID, 2;	-- Close
 */